﻿using System;
using System.Collections.Generic;
using System.Data;
using System.Data.Common;
using System.Linq;
using System.Text;
using BadmintonApp.Entity;
using BadmintonApp.DAO.Interface;

namespace BadmintonApp.DAO.Implement
{
    public class AnnouncementDAO : BaseDAO, IAnnouncementDAO
    {
        private int GetMaxAnnoID()
        {
            string sql = "Select IsNull(Max(FAnnoID), 0) from TAnnouncements";
            DbCommand selectCmd = db.GetSqlStringCommand(sql);
            return (int)db.ExecuteScalar(selectCmd);
        }

        #region IAnnouncementDAO Members

        /// <summary>
        /// 插入一个公告
        /// </summary>
        /// <param name="annoInfo">公告信息类</param>
        public void InsertAnnouncement(TAnnouncementsInfo annoInfo)
        {
            StringBuilder sbSQL = new StringBuilder();
            sbSQL.Append("Insert into TAnnouncements(FAnnoID, FTitle, FContent, FCreateDate, ");
            sbSQL.Append(" FPublishedDate, FPublisherID) Values (@FAnnoID, @FTitle, @FContent, @FCreateDate), ");
            sbSQL.Append(" @FPublishedDate, @FPublisherID)");

            DbCommand insertCmd = db.GetSqlStringCommand(sbSQL.ToString());
            annoInfo.FAnnoID = GetMaxAnnoID() + 1;
            db.AddInParameter(insertCmd, "FAnnoID", DbType.Int32, annoInfo.FAnnoID);
            db.AddInParameter(insertCmd, "FTitle", DbType.String, annoInfo.FTitle);
            db.AddInParameter(insertCmd, "FContent", DbType.String, annoInfo.FContent);
            db.AddInParameter(insertCmd, "FCreateDate", DbType.Date, annoInfo.FCreateDate);
            db.AddInParameter(insertCmd, "FPublishedDate", DbType.Date, annoInfo.FPublishedDate);
            db.AddInParameter(insertCmd, "FPublisherID", DbType.Int32, annoInfo.FPublisherID);
            db.ExecuteNonQuery(insertCmd);
        }

        /// <summary>
        /// 更新一个公告
        /// </summary>
        /// <param name="annoInfo">公告信息类</param>
        public void UpdateAnnouncement(TAnnouncementsInfo annoInfo)
        {
            StringBuilder sbSQL = new StringBuilder();
            sbSQL.Append("Update TAnnouncements Set FTitle=@FTitle, FContent=@FContent, FCreateDate=@FCreateDate, ");
            sbSQL.Append(" FPublishedDate=@FPublishedDate, FPublisherID=@FPublisherID Where FAnnoID=@FAnnoID");

            DbCommand updateCmd = db.GetSqlStringCommand(sbSQL.ToString());
            db.AddInParameter(updateCmd, "FAnnoID", DbType.Int32, annoInfo.FAnnoID);
            db.AddInParameter(updateCmd, "FTitle", DbType.String, annoInfo.FTitle);
            db.AddInParameter(updateCmd, "FContent", DbType.String, annoInfo.FContent);
            db.AddInParameter(updateCmd, "FCreateDate", DbType.Date, annoInfo.FCreateDate);
            db.AddInParameter(updateCmd, "FPublishedDate", DbType.Date, annoInfo.FPublishedDate);
            db.AddInParameter(updateCmd, "FPublisherID", DbType.Int32, annoInfo.FPublisherID);
            db.ExecuteNonQuery(updateCmd);
        }

        /// <summary>
        /// 删除一个公告
        /// </summary>
        /// <param name="annoID">公告ID</param>
        public void DeleteAnnouncement(int annoID)
        {
            string sql = "Delete from TAnnouncements Where FAnnoID=@FAnnoID";
            DbCommand deleteCmd = db.GetSqlStringCommand(sql);
            db.AddInParameter(deleteCmd, "FAnnoID", DbType.Int32, annoID);
            db.ExecuteNonQuery(deleteCmd);
        }

        /// <summary>
        /// 获取所有公告
        /// </summary>
        /// <returns></returns>
        public DataSet GetAllAnnouncement()
        {
            string sql = "Select * from TAnnouncements";
            DbCommand selectCmd = db.GetSqlStringCommand(sql);
            DataSet dsAnno = db.ExecuteDataSet(selectCmd);
            return dsAnno;
        }

        #endregion
    }
}
